﻿using System;
using System.Data;
using System.Data.SqlClient;
using MyHealthByDesign.Domain;
using System.Collections.Generic;

namespace MyHealthByDesign.DataAccess.AccountDA
{
    /// <summary>
    /// Description:
    /// AccountDao class provides core data access to SQL stored procedures.
    /// 
    /// Author: Sanjeev Shrestha
    /// Date Created: 10/17/2011
    /// 
    /// Other code files referenced:
    /// DaoBase.cs
    /// 
    /// Regression Test Information:
    /// AccountDA_Test.cs in project MyHealthByDesign_Test
    /// 
    /// Revision History:
    /// 10/19/2011 Sanjeev Shrestha - Updated logic to not create new instance of User object if reader is null.
    /// 10/20/2011 Sanjeev Shrestha - Renamed username text to email. Added user object in session variable.
    /// 10/21/2011 Sanjeev Shrestha - Updated code to register new user in database.
    /// 10/27/2011 Mark Servais - Added methods for Question database processing
    /// 10/27/2011 Sanjeev Shrestha - Added methods for Question maintenance
    /// 10/30/2011 Mark Servais - Fixed reader bugs with shared connection among procedures
    /// 11/03/2011 Sanjeev Shrestha - Added birthdate and textflag to user object
    /// 11/03/2011 Mark Servais - Added methods to retreive certification date, added OPtionCSS fields,
    ///                             and added method to retrieve question answers by field name
    /// 11/08/2011 Mark Servais - Made changes to retreive user and bring back Identity value to populate in user object.
    /// 11/18/2011 Mark Servais - Added Help URL link for options
    /// 11/19/2011 Mark Servais - Added logic for Processing specials
    /// 12/02/2011 Mark Servais - Added routines for category maintenance
    /// 12/03/2011 Mark Servais - Added routines for question maintenance
    /// 12/10/2011 Mark Servais - Added routines for option maintenance
    /// 
    /// Known Faults:
    /// None.
    /// </summary>
	public class AccountDao : DaoBase
	{
        /// <summary>
        /// Class Constructor
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// Class connectionstring is set based on parameter
        /// </summary>
        /// <param name="connectionString"></param>
		public AccountDao(string connectionString)
			:base(connectionString)
		{
			
		}
        
        /// <summary>
        /// GetUserInfo - Retireves User data and creates User object
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// User object is returned based on parameters passed
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns></returns>
		public User GetUserInfo(string email, string password)
		{
			User user = null;

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetUserInfo", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@email", email);
					command.Parameters.AddWithValue("@password", password);

					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader.HasRows && reader.Read())
						{
							user = new User
									   {
										   UserId = (int) reader["UserId"],
										   Email = reader["Email"].ToString().Trim(),
										   Password = reader["Password"].ToString().Trim(),
										   Firstname = reader["Firstname"].ToString().Trim(),
										   Lastname = reader["Lastname"].ToString().Trim(),
                                           BirthDate = reader["BirthDate"].ToString().Trim(),
										   Gender = reader["Gender"].ToString().Trim(),
										   Ethnicity = reader["Ethnicity"].ToString().Trim(),
										   StreetAddress = DBNull.Value != reader["StreetAddress"] ? reader["StreetAddress"].ToString().Trim() : string.Empty,
										   City = DBNull.Value != reader["City"] ? reader["City"].ToString().Trim() : string.Empty,
										   State = DBNull.Value != reader["State"] ? reader["State"].ToString().Trim() : string.Empty,
										   Country = DBNull.Value != reader["Country"] ? reader["Country"].ToString().Trim() : string.Empty,
										   MobilePhone = DBNull.Value != reader["MobilePhone"] ? reader["MobilePhone"].ToString().Trim() : string.Empty,
                                           TextFlag = DBNull.Value != reader["TextFlag"] ? reader["TextFlag"].ToString().Trim(): string.Empty,
                                           Administrator = DBNull.Value != reader["Administrator"] ? reader["Administrator"].ToString().Trim() : string.Empty
									   };
						} 
					} 
				}
			}   

			return user;
		}

        /// <summary>
        /// GetPassword - Retireves User password based on email
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// String representing password is returned
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
		public string GetPassword(string email)
		{
			string password = string.Empty;

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetPassword", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@email", email);
					
					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader.HasRows && reader.Read())
						{
							password = reader["Password"].ToString().Trim();
						}
					}
				}
			}

			return password;
		}

        /// <summary>
        /// RegsiterUser - Creates a new user in the database
        /// 
        /// PreConditions:
        /// User does not already exist via provided email
        /// 
        /// PostConditions:
        /// Integer value that represents the UserID in the database
        /// </summary>
        /// <param name="lastname"></param>
        /// <param name="firstname"></param>
        /// <param name="birthDate"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zipCode"></param>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <param name="mobile"></param>
        /// <param name="textFlag"></param>
        /// <param name="sex"></param>
        /// <param name="ethnicity"></param>
        /// <returns></returns>
        public int RegsiterUser(string lastname, string firstname, string birthDate, string city, string state, string zipCode, string email, string password, string mobile, string textFlag, string sex, string ethnicity)
		{
			int insertedUserId = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert New User");

				using (var command = new SqlCommand("dbo.RegisterUser", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@email", email);
						command.Parameters.AddWithValue("@password", password);
						command.Parameters.AddWithValue("@lastname", lastname);
						command.Parameters.AddWithValue("@firstname", firstname);
						command.Parameters.AddWithValue("@birthDate", birthDate);
						command.Parameters.AddWithValue("@gender", sex);
						command.Parameters.AddWithValue("@ethnicity", ethnicity);
						command.Parameters.AddWithValue("@streetAddress", DBNull.Value);
						command.Parameters.AddWithValue("@city", city);
						command.Parameters.AddWithValue("@state", state);
						command.Parameters.AddWithValue("@zipCode", zipCode);
						command.Parameters.AddWithValue("@mobilePhone", !string.IsNullOrWhiteSpace(mobile) ? mobile : "NULL");
						command.Parameters.AddWithValue("@country", "US");
						command.Parameters.AddWithValue("@textFlag", !string.IsNullOrWhiteSpace(textFlag) ? textFlag : "N");
                        
                        // create return parameter
                        SqlParameter parmRet = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
					    parmRet.Direction = ParameterDirection.ReturnValue;
					    command.Parameters.Add(parmRet);

                        using (var reader = command.ExecuteReader())
                        {
                            insertedUserId = Convert.ToInt32(parmRet.Value);
                        }
						
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

            return insertedUserId;
		}

        /// <summary>
        /// GetQuestionsForCategory - Retrieves all Questions from the database
        /// related to the provided categoryId value
        /// 
        /// PreConditions:
        /// Category needs to exist in the system
        /// 
        /// PostConditions:
        /// List of questions objects is returned
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
		public List<Question> GetQuestionsForCategory(int categoryId)
		{

			List<Question> lstQ = new List<Question>(); // return variable

			SqlCommand command = null; //command for getting questions
			SqlCommand command2 = null; // command for getting options for a question
			SqlCommand command3 = null; // command for getting special accommodations for an option
            SqlCommand command4 = null; // command for getting question processing

			SqlDataAdapter da = null; // data adapter for questions
			SqlDataAdapter da2 = null; // data adapter for options
			SqlDataAdapter da3 = null; // data adapter for specials
            SqlDataAdapter da4 = null; // data adapater for question processing

			DataTable reader = new DataTable(); // data table for questions
			DataTable reader2 = new DataTable(); // data table for options
			DataTable reader3 = new DataTable(); // data table for specials
            DataTable reader4 = new DataTable(); // data table for processing

			using (SqlConnection conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				
				command = new SqlCommand("dbo.GetQuestionsByCategory", conn);
				command.CommandType = CommandType.StoredProcedure;
				command.Parameters.AddWithValue("@categoryID", categoryId);
				
				using (da = new SqlDataAdapter(command))
				{
                    // get questions by category routine
                    reader.Clear();
					da.Fill(reader);

                    // loop through questions, build question object,
                    // and retreive options
					foreach(DataRow dr in reader.Rows)
					{
                        // create and instance of a question from data
						Question question = new Question();
                        question.Id = (int)dr["QuestionID"];
                        question.Category = (int)dr["QuestionCategoryID"];
                        question.SortOrder = (int)dr["QuestionSortOrder"];
                        question.DisplayType = dr["DisplayType"].ToString().Trim();
                        question.Name = dr["FieldName"].ToString().Trim();
                        question.ContentLabel = dr["FieldLabel"].ToString().Trim();
                        question.FieldCSS = DBNull.Value != dr["FieldCSS"]
										 ? dr["FieldCSS"].ToString().Trim()
                                         : string.Empty;
                        question.FieldSuffix = DBNull.Value != dr["FieldSuffix"]
											? dr["FieldSuffix"].ToString().Trim()
                                            : string.Empty;
                        question.FieldHelpText = DBNull.Value != dr["FieldHelpText"]
											  ? dr["FieldHelpText"].ToString().Trim()
                                              : string.Empty;
                        question.FieldHelpURL = DBNull.Value != dr["FieldHelpURL"]
											 ? dr["FieldHelpURL"].ToString().Trim()
                                             : string.Empty;
					    question.FieldActive = Convert.ToBoolean(dr["QuestionActive"]);

						// get options for question
						List<QuestionOption> lstQuestionOption = new List<QuestionOption>();

						command2 = new SqlCommand("dbo.GetOptionsByQuestion", conn);
						command2.CommandType = CommandType.StoredProcedure;
						command2.Parameters.AddWithValue("@questionID", question.Id);
						
						using(da2 = new SqlDataAdapter(command2))
						{
                            // get options for question
                            reader2.Clear();
							da2.Fill(reader2);

                            // loop through question options and determine if
                            // options have specials
							foreach(DataRow dr2 in reader2.Rows)
							{
								QuestionOption questionOption = new QuestionOption();

                                questionOption.Id = (int)dr2["OptionID"];
                                questionOption.Name = dr2["OptionName"].ToString().Trim();
                                questionOption.SortOrder = (int)dr2["OptionSortOrder"];
                                questionOption.OptionLabel = dr2["OptionLabel"].ToString().Trim();
                                questionOption.OptionCSS = dr2["OptionCSS"].ToString().Trim();
							    questionOption.OptionHelpURL = dr2["OptionHelpURL"].ToString().Trim();

								//get specials for option
								List<OptionSpecial> lstOptionSpecial = new List<OptionSpecial>();

								command3 = new SqlCommand("dbo.GetSpecialsByOption", conn);
								command3.CommandType = CommandType.StoredProcedure;
								command3.Parameters.AddWithValue("@optionID", questionOption.Id);

								using (da3 = new SqlDataAdapter(command3))
								{
                                    // get specials for option
                                    reader3.Clear();
									da3.Fill(reader3);

                                    // loop through question options and add to collection
									foreach (DataRow dr3 in reader3.Rows)
									{
										OptionSpecial optionSpecial = new OptionSpecial();

                                        optionSpecial.Id = (int)dr3["SpecialID"];
                                        optionSpecial.Name = dr3["SpecialName"].ToString().Trim();
                                        optionSpecial.DisplayType = dr3["SpecialDisplayType"].ToString().Trim();

                                        lstOptionSpecial.Add(optionSpecial);
									}

								}

                                questionOption.Specials = lstOptionSpecial; // add specials collection to option

                                lstQuestionOption.Add(questionOption); 
							}
						}
                        
                        question.FieldOptions = lstQuestionOption; // add options collection to question

                        // get processing items
                        command4 = new SqlCommand("dbo.GetProcessingByQuestion", conn);
				        command4.CommandType = CommandType.StoredProcedure;
				        command4.Parameters.AddWithValue("@questionID", question.Id);

                        List<QuestionProcessing> listProcessing = new List<QuestionProcessing>();
                        
                        using (da4 = new SqlDataAdapter(command4))
                        {
                            reader4.Clear();
                            da4.Fill(reader4);

                            
                            // loop through question options and add to collection
                            foreach (DataRow dr4 in reader4.Rows)
                            {
                                QuestionProcessing questionProcessing = new QuestionProcessing();
                                
                                questionProcessing.Id = (int)dr4["QuestionProcessingID"];
                                questionProcessing.QuestionId = (int)dr4["QuestionID"];
                                questionProcessing.FieldName = dr4["FieldName"].ToString().Trim();
                                questionProcessing.QuestionProcessingType = dr4["QuestionProcessingType"].ToString().Trim();
                                questionProcessing.QuestionProcessingValues = dr4["QuestionProcessingValues"].ToString().Trim();

                                listProcessing.Add(questionProcessing);
                            }
                        }

					    question.Processing = listProcessing;

                        question.Attributes = new List<HtmlAttribute>();

                        lstQ.Add(question); // add question to output variable
					}
				}
				
				conn.Close();
			}

			return lstQ;
		}

        /// <summary>
        /// GetUserAnswers - Retrieves all question answers for a UserId
        /// 
        /// PreConditions:
        /// User needs to exist in the system
        /// 
        /// PostConditions:
        /// List of question answers is returned
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
		public List<QuestionAnswer> GetUserAnswers(int userId)
		{
			var questionAnswerLists = new List<QuestionAnswer>();

			using (var conn = new SqlConnection(ConnectionString))
			{
				using (var command = new SqlCommand("dbo.GetUserAnswers", conn))
				{
					command.CommandType = CommandType.StoredProcedure;
					command.Parameters.AddWithValue("@userID", userId);

					conn.Open();

					using (var reader = command.ExecuteReader())
					{
						while (reader.HasRows && reader.Read())
						{
							var questionAnswer = new QuestionAnswer
													 {
														 AnswerId = (int) reader["AnswerID"],
														 QuestionId = (int) reader["QuestionID"],
														 UserId = userId,
														 FieldName = (string) reader["FieldName"],
														 FieldValue = (string) reader["FieldValue"]
													 };
							questionAnswerLists.Add(questionAnswer);
						}
					}
				}
			}

			return questionAnswerLists;
		}

        /// <summary>
        /// InsertOptionSpecial - Adds a special for an option to the database
        /// 
        /// PreConditions:
        /// Option needs to exist in the system
        /// 
        /// PostConditions:
        /// Returns number of rows inserted
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="specialName"></param>
        /// <param name="specialDisplayType"></param>
        /// <returns></returns>
		public int InsertOptionSpecial(int optionId, string specialName, string specialDisplayType)
		{
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertOptionSpecial", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						command.Parameters.AddWithValue("@specialName", specialName);
						command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

        /// <summary>
        /// InsertQuestion - Adds a question to the database
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// Returns number of rows inserted
        /// </summary>
        /// <param name="questionCategoryId"></param>
        /// <param name="questionSortOrder"></param>
        /// <param name="displayType"></param>
        /// <param name="fieldName"></param>
        /// <param name="fieldLabel"></param>
        /// <param name="fieldCss"></param>
        /// <param name="fieldSuffix"></param>
        /// <param name="fieldHelpText"></param>
        /// <param name="fieldHelpUrl"></param>
        /// <returns></returns>
		public int InsertQuestion(int questionCategoryId, int questionSortOrder, string displayType, string fieldName,
								  string fieldLabel, string fieldCss, string fieldSuffix, string fieldHelpText, string fieldHelpUrl)
		{
	
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert Question");

				using (var command = new SqlCommand("dbo.InsertQuestion", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionCategoryID", questionCategoryId);
						command.Parameters.AddWithValue("@questionSortOrder", questionSortOrder);
						command.Parameters.AddWithValue("@displayType", displayType);
						command.Parameters.AddWithValue("@fieldName", fieldName);
						command.Parameters.AddWithValue("@fieldLabel", fieldLabel);
						command.Parameters.AddWithValue("@fieldCSS", fieldCss);
						command.Parameters.AddWithValue("@fieldSuffix", fieldSuffix);
						command.Parameters.AddWithValue("@fieldHelpText", fieldHelpText);
						command.Parameters.AddWithValue("@fieldHelpUrl", fieldHelpUrl);

					    SqlParameter spReturn = new SqlParameter("@Return_Value", SqlDbType.Int);
					    spReturn.Direction = ParameterDirection.ReturnValue;
					    command.Parameters.Add(spReturn);

					    command.ExecuteNonQuery();

					    insertRowsCount = Int32.Parse(command.Parameters["@Return_Value"].Value.ToString());
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

        /// <summary>
        /// InsertQuestionOption - Adds a question option to the database
        /// 
        /// PreConditions:
        /// Question must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of rows inserted
        /// </summary>
        /// <param name="questionId"></param>
        /// <param name="optionName"></param>
        /// <param name="optionSortOrder"></param>
        /// <param name="optionLabel"></param>
        /// <param name="optionCSS"></param>
        /// <returns></returns>
		public int InsertQuestionOption(int questionId, string optionName, int optionSortOrder, string optionLabel, string optionCSS)
		{
			int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertQuestionOption", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);
						command.Parameters.AddWithValue("@optionName", optionName);
						command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
						command.Parameters.AddWithValue("@optionLabel", optionLabel);
                        command.Parameters.AddWithValue("@optionCSS", optionCSS);

						insertRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return insertRowsCount;
		}

        /// <summary>
        /// InsertUserAnswers - Adds a question answer for a user
        /// to the database
        /// 
        /// PreConditions:
        /// User must exist in the system
        /// 
        /// PostConditions:
        /// Returns boolean value if answers were added successfully
        /// </summary>
        /// <param name="lstQuestionAnswers"></param>
        /// <returns></returns>
		public bool InsertUserAnswers(List<QuestionAnswer> lstQuestionAnswers)
		{
		    bool bSuccess = false;
		    int insertRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.InsertUserAnswers", conn, transaction))
				{
					try
					{
                        foreach (QuestionAnswer questionAnswer in lstQuestionAnswers)
					    {
					    
					        command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@questionID", questionAnswer.QuestionId);
                            command.Parameters.AddWithValue("@userID", questionAnswer.UserId);
                            command.Parameters.AddWithValue("@fieldName", questionAnswer.FieldName);
                            command.Parameters.AddWithValue("@fieldValue", questionAnswer.FieldValue);

					        insertRowsCount = command.ExecuteNonQuery();

                            if(insertRowsCount == 0)
                                throw new Exception("Not Inserted");
					    }
				        transaction.Commit();
					    bSuccess = true;
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return bSuccess;
		}

        /// <summary>
        /// UpdateQuestion - Updates a question in the database
        /// 
        /// PreConditions:
        /// Question must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="questionId"></param>
        /// <param name="questionCategoryId"></param>
        /// <param name="questionSortOrder"></param>
        /// <param name="displayType"></param>
        /// <param name="fieldLabel"></param>
        /// <param name="fieldCss"></param>
        /// <param name="fieldSuffix"></param>
        /// <param name="fieldHelpText"></param>
        /// <param name="fieldHelpUrl"></param>
        /// <returns></returns>
		public int UpdateQuestion(int questionId, int questionCategoryId, int questionSortOrder, string displayType,
								  string fieldLabel, string fieldCss, string fieldSuffix, string fieldHelpText, string fieldHelpUrl)
		{

			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestion", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);
						command.Parameters.AddWithValue("@questionCategoryID", questionCategoryId);
						command.Parameters.AddWithValue("@questionSortOrder", questionSortOrder);
						command.Parameters.AddWithValue("@displayType", displayType);
						command.Parameters.AddWithValue("@fieldLabel", fieldLabel);
						command.Parameters.AddWithValue("@fieldCSS", fieldCss);
						command.Parameters.AddWithValue("@fieldSuffix", fieldSuffix);
						command.Parameters.AddWithValue("@fieldHelpText", fieldHelpText);
						command.Parameters.AddWithValue("@fieldHelpUrl", fieldHelpUrl);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateQuestionActiveStatus - Updates a question active status
        /// in the database
        /// 
        /// PreConditions:
        /// Question must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="questionId"></param>
        /// <returns></returns>
		public int UpdateQuestionActiveStatus(int questionId)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionActiveStatus", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@questionID", questionId);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateQuestionOption - Updates a question option in the database
        /// 
        /// PreConditions:
        /// Question Option must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="optionSortOrder"></param>
        /// <param name="optionLabel"></param>
        /// <param name="optionCSS"></param>
        /// <returns></returns>
		public int UpdateQuestionOption(int optionId, int optionSortOrder, string optionLabel, string optionCSS)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOption", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
						command.Parameters.AddWithValue("@optionLabel", optionLabel);
                        command.Parameters.AddWithValue("@optionCSS", optionCSS);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateQuestionOptionActiveStatus - Updates a question option active status
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionId"></param>
        /// <returns></returns>
		public int UpdateQuestionOptionActiveStatus(int optionId)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOptionActiveStatus", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@optionID", optionId);
						
						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateQuestionOptionSpecial - Updates a question option special
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option Special must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="specialId"></param>
        /// <param name="specialDisplayType"></param>
        /// <returns></returns>
		public int UpdateQuestionOptionSpecial(int specialId, string specialDisplayType)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateQuestionOptionSpecial", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@specialID", specialId);
						command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateUserAnswers - Updates a question answers in the database
        /// for a provided UserId
        /// 
        /// PreConditions:
        /// User and question answer must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="answerId"></param>
        /// <param name="fieldName"></param>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
		public int UpdateUserAnswers(int answerId, string fieldName, string fieldValue)
		{
			int updateRowsCount = 0;

			using (var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();

				//set a local transaction
				var transaction = conn.BeginTransaction("Insert User Answer");

				using (var command = new SqlCommand("dbo.UpdateUserAnswers", conn, transaction))
				{
					try
					{
						command.CommandType = CommandType.StoredProcedure;
						command.Parameters.AddWithValue("@answerID", answerId);
						command.Parameters.AddWithValue("@fieldName", fieldName);
						command.Parameters.AddWithValue("@fieldValue", fieldValue);

						updateRowsCount = command.ExecuteNonQuery();
						transaction.Commit();
					}
					catch (Exception ex)
					{
						transaction.Rollback();
					}
				}
			}

			return updateRowsCount;
		}

        /// <summary>
        /// UpdateCertificationDate - Updates certification date in the database
        /// for a provided UserId
        /// 
        /// PreConditions:
        /// User must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int UpdateCertificationDate(int userId)
        {
            int updateRowsCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Update User Certification");

                using (var command = new SqlCommand("dbo.UpdateCertificationDate", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@userID", userId);

                        updateRowsCount = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return updateRowsCount;
        }

        /// <summary>
        /// UpdateDisclaimerDate - Updates disclaimer date in the database
        /// for a provided UserId
        /// 
        /// PreConditions:
        /// User must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int UpdateDisclaimerDate(int userId)
        {
            int updateRowsCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Update User Disclaimer");

                using (var command = new SqlCommand("dbo.UpdateDisclaimerDate", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@userID", userId);

                        updateRowsCount = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return updateRowsCount;
        }

        /// <summary>
        /// GetCertificationDate - Retrieves certification date in the database
        /// for a provided UserId
        /// 
        /// PreConditions:
        /// User must exist in the system
        /// 
        /// PostConditions:
        /// Returns datetime object representing the certification date
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DateTime GetCertificationDate(int userId)
        {
            DateTime dtCertification = DateTime.MinValue;
            
            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("dbo.GetCertificationDate", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@userId", userId);

                    conn.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            if (reader["CertificationDate"] != DBNull.Value)
                                dtCertification = (DateTime)reader["CertificationDate"];
                        }
                    }
                }
            }

            return dtCertification;
        }

        /// <summary>
        /// GetUserAnswerByField - Retrieves question answer in the database
        /// for a provided UserId and question field name
        /// 
        /// PreConditions:
        /// User and question answer field name must exist in the system
        /// 
        /// PostConditions:
        /// Returns QuestionAnswer boject representing a question answer 
        /// for a field name and userId
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public QuestionAnswer GetUserAnswerByField(int userId, string fieldName)
        {
            QuestionAnswer questionAnswer = new QuestionAnswer();

            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("dbo.GetUserAnswerByField", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@userID", userId);
                    command.Parameters.AddWithValue("@questionField", fieldName);

                    conn.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            questionAnswer = new QuestionAnswer
                            {
                                AnswerId = (int)reader["AnswerID"],
                                QuestionId = (int)reader["QuestionID"],
                                UserId = userId,
                                FieldName = (string)reader["FieldName"],
                                FieldValue = (string)reader["FieldValue"]
                            };
                            break;
                        }
                    }
                }
            }

            return questionAnswer;
        }

        /// <summary>
        /// GetCategories - Retrieves all question categories
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// List of question categories is returned
        /// </summary>
        /// <returns></returns>
        public List<QuestionCategory> GetCategories()
        {
            var questionCategoryLists = new List<QuestionCategory>();

            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("dbo.GetAllCategories", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    
                    conn.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.HasRows && reader.Read())
                        {
                            var questionCategory = new QuestionCategory
                            {
                                CategoryId = (int)reader["CategoryID"],
                                CategoryDescription = (string)reader["CategoryDescription"]
                            };
                            questionCategoryLists.Add(questionCategory);
                        }
                    }
                }
            }

            return questionCategoryLists;
        }

        /// <summary>
        /// InsertCategory - Adds a category to the database
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// Returns identity of row inserted
        /// </summary>
       /// <param name="categoryDescription"></param>
        /// <returns></returns>
        public int InsertCategory(string categoryDescription)
        {
            int categoryIdentity = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Insert Category");

                using (var command = new SqlCommand("dbo.InsertCategory", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@categoryDescription", categoryDescription);

                        categoryIdentity = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return categoryIdentity;
        }

        /// <summary>
        /// UpdateCategory - Update a category in the database
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// Returns boolean of update success
        /// </summary>
        /// <param name="categoryId"></param>
        /// <param name="categoryDescription"></param>
        /// <returns></returns>
        public bool UpdateCategory(int categoryId, string categoryDescription)
        {
            bool bValid = false;
            int nRowCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Update Category");

                using (var command = new SqlCommand("dbo.UpdateCategory", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@categoryID", categoryId);
                        command.Parameters.AddWithValue("@categoryDescription", categoryDescription);

                        nRowCount = command.ExecuteNonQuery();
                        transaction.Commit();
                        if(nRowCount > 0)
                            bValid = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return bValid;
        }

        /// <summary>
        /// DeleteCategory - Delete a category from the database
        /// 
        /// PreConditions:
        /// None
        /// 
        /// PostConditions:
        /// Returns boolean of deletion success
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public bool DeleteCategory(int categoryId)
        {
            bool bValid = false;
            int nRowCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Delete Category");

                using (var command = new SqlCommand("dbo.DeleteCategory", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@categoryID", categoryId);
                        
                        nRowCount = command.ExecuteNonQuery();
                        transaction.Commit();
                        if (nRowCount > 0)
                            bValid = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return bValid;
        }

        /// <summary>
        /// GetQuestionById - Retrieves Question from the database
        /// related to the provided questionId value
        /// 
        /// PreConditions:
        /// Question needs to exist in the system
        /// 
        /// PostConditions:
        /// question object is returned
        /// </summary>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public Question GetQuestionById(int questionId)
        {

            Question returnQuestion = new Question(); // return variable

            SqlCommand command = null; //command for getting questions
            SqlCommand command2 = null; // command for getting options for a question
            SqlCommand command3 = null; // command for getting special accommodations for an option
            SqlCommand command4 = null; // command for getting question processing

            SqlDataAdapter da = null; // data adapter for questions
            SqlDataAdapter da2 = null; // data adapter for options
            SqlDataAdapter da3 = null; // data adapter for specials
            SqlDataAdapter da4 = null; // data adapater for question processing

            DataTable reader = new DataTable(); // data table for questions
            DataTable reader2 = new DataTable(); // data table for options
            DataTable reader3 = new DataTable(); // data table for specials
            DataTable reader4 = new DataTable(); // data table for processing

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                command = new SqlCommand("dbo.GetQuestionByQuestionID", conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@questionID", questionId);

                using (da = new SqlDataAdapter(command))
                {
                    // get questions by category routine
                    reader.Clear();
                    da.Fill(reader);

                    // loop through questions, build question object,
                    // and retreive options
                    foreach (DataRow dr in reader.Rows)
                    {
                        // create and instance of a question from data
                        Question question = new Question();
                        question.Id = (int)dr["QuestionID"];
                        question.Category = (int)dr["QuestionCategoryID"];
                        question.SortOrder = (int)dr["QuestionSortOrder"];
                        question.DisplayType = dr["DisplayType"].ToString().Trim();
                        question.Name = dr["FieldName"].ToString().Trim();
                        question.ContentLabel = dr["FieldLabel"].ToString().Trim();
                        question.FieldCSS = DBNull.Value != dr["FieldCSS"]
                                         ? dr["FieldCSS"].ToString().Trim()
                                         : string.Empty;
                        question.FieldSuffix = DBNull.Value != dr["FieldSuffix"]
                                            ? dr["FieldSuffix"].ToString().Trim()
                                            : string.Empty;
                        question.FieldHelpText = DBNull.Value != dr["FieldHelpText"]
                                              ? dr["FieldHelpText"].ToString().Trim()
                                              : string.Empty;
                        question.FieldHelpURL = DBNull.Value != dr["FieldHelpURL"]
                                             ? dr["FieldHelpURL"].ToString().Trim()
                                             : string.Empty;

                        question.FieldActive = Convert.ToBoolean(dr["QuestionActive"]);

                        // get options for question
                        List<QuestionOption> lstQuestionOption = new List<QuestionOption>();

                        command2 = new SqlCommand("dbo.GetOptionsByQuestion", conn);
                        command2.CommandType = CommandType.StoredProcedure;
                        command2.Parameters.AddWithValue("@questionID", question.Id);

                        using (da2 = new SqlDataAdapter(command2))
                        {
                            // get options for question
                            reader2.Clear();
                            da2.Fill(reader2);

                            // loop through question options and determine if
                            // options have specials
                            foreach (DataRow dr2 in reader2.Rows)
                            {
                                QuestionOption questionOption = new QuestionOption();

                                questionOption.Id = (int)dr2["OptionID"];
                                questionOption.Name = dr2["OptionName"].ToString().Trim();
                                questionOption.SortOrder = (int)dr2["OptionSortOrder"];
                                questionOption.OptionLabel = dr2["OptionLabel"].ToString().Trim();
                                questionOption.OptionCSS = dr2["OptionCSS"].ToString().Trim();
                                questionOption.OptionHelpURL = dr2["OptionHelpURL"].ToString().Trim();

                                //get specials for option
                                List<OptionSpecial> lstOptionSpecial = new List<OptionSpecial>();

                                command3 = new SqlCommand("dbo.GetSpecialsByOption", conn);
                                command3.CommandType = CommandType.StoredProcedure;
                                command3.Parameters.AddWithValue("@optionID", questionOption.Id);

                                using (da3 = new SqlDataAdapter(command3))
                                {
                                    // get specials for option
                                    reader3.Clear();
                                    da3.Fill(reader3);

                                    // loop through question options and add to collection
                                    foreach (DataRow dr3 in reader3.Rows)
                                    {
                                        OptionSpecial optionSpecial = new OptionSpecial();

                                        optionSpecial.Id = (int)dr3["SpecialID"];
                                        optionSpecial.Name = dr3["SpecialName"].ToString().Trim();
                                        optionSpecial.DisplayType = dr3["SpecialDisplayType"].ToString().Trim();

                                        lstOptionSpecial.Add(optionSpecial);
                                    }

                                }

                                questionOption.Specials = lstOptionSpecial; // add specials collection to option

                                lstQuestionOption.Add(questionOption);
                            }
                        }

                        question.FieldOptions = lstQuestionOption; // add options collection to question

                        // get processing items
                        command4 = new SqlCommand("dbo.GetProcessingByQuestion", conn);
                        command4.CommandType = CommandType.StoredProcedure;
                        command4.Parameters.AddWithValue("@questionID", question.Id);

                        List<QuestionProcessing> listProcessing = new List<QuestionProcessing>();

                        using (da4 = new SqlDataAdapter(command4))
                        {
                            reader4.Clear();
                            da4.Fill(reader4);


                            // loop through question options and add to collection
                            foreach (DataRow dr4 in reader4.Rows)
                            {
                                QuestionProcessing questionProcessing = new QuestionProcessing();

                                questionProcessing.Id = (int)dr4["QuestionProcessingID"];
                                questionProcessing.QuestionId = (int)dr4["QuestionID"];
                                questionProcessing.FieldName = dr4["FieldName"].ToString().Trim();
                                questionProcessing.QuestionProcessingType = dr4["QuestionProcessingType"].ToString().Trim();
                                questionProcessing.QuestionProcessingValues = dr4["QuestionProcessingValues"].ToString().Trim();

                                listProcessing.Add(questionProcessing);
                            }
                        }

                        question.Processing = listProcessing;

                        question.Attributes = new List<HtmlAttribute>();

                        returnQuestion = question; // move question to output variable
                        break;
                    }
                }

                conn.Close();
            }

            return returnQuestion;
        }

        /// <summary>
        /// UpdateQuestionActiveStatusByValue - Updates a question active status
        /// in the database by provided value
        /// 
        /// PreConditions:
        /// Question must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public int UpdateQuestionActiveStatusByValue(int questionId, bool questionActive)
        {
            int updateRowsCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Update Question Status");

                using (var command = new SqlCommand("dbo.UpdateQuestionActiveStatusByValue", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@questionID", questionId);
                        command.Parameters.AddWithValue("@questionActive", questionActive);

                        updateRowsCount = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return updateRowsCount;
        }

        /// <summary>
        /// GetQuestionOptions - Retrieves all question options for a QuestionId
        /// 
        /// PreConditions:
        /// Question needs to exist in the system
        /// 
        /// PostConditions:
        /// List of question options is returned
        /// </summary>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public List<QuestionOption> GetQuestionOptions(int questionId)
        {
            var questionOptionList = new List<QuestionOption>();

            SqlCommand command2 = null; // command for getting options for a question

            SqlDataAdapter da = null; // data adapter for options
            SqlDataAdapter da2 = null; // data adapter for optionSpecials

            DataTable reader = new DataTable();
            DataTable reader2 = new DataTable();

            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var command = new SqlCommand("dbo.GetOptionsByQuestion", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@questionID", questionId);

                    conn.Open();

                    using (da = new SqlDataAdapter(command))
                    {
                        reader.Clear();
                        da.Fill(reader);

                        foreach (DataRow dr in reader.Rows)
                        {
                            QuestionOption questionOption = new QuestionOption();

                            questionOption.Id = (int)dr["OptionID"];
                            questionOption.Name = dr["OptionName"].ToString().Trim();
                            questionOption.SortOrder = (int)dr["OptionSortOrder"];
                            questionOption.OptionLabel = dr["OptionLabel"].ToString().Trim();
                            questionOption.OptionCSS = dr["OptionCSS"].ToString().Trim();
                            questionOption.OptionHelpURL = dr["OptionHelpURL"].ToString().Trim();

							//get specials for option
							List<OptionSpecial> lstOptionSpecial = new List<OptionSpecial>();

                            command2 = new SqlCommand("dbo.GetSpecialsByOption", conn);
							command2.CommandType = CommandType.StoredProcedure;
							command2.Parameters.AddWithValue("@optionID", questionOption.Id);

								using (da2 = new SqlDataAdapter(command2))
								{
                                    // get specials for option
                                    reader2.Clear();
									da2.Fill(reader2);

                                    // loop through question options and add to collection
									foreach (DataRow dr2 in reader2.Rows)
									{
										OptionSpecial optionSpecial = new OptionSpecial();

                                        optionSpecial.Id = (int)dr2["SpecialID"];
                                        optionSpecial.Name = dr2["SpecialName"].ToString().Trim();
                                        optionSpecial.DisplayType = dr2["SpecialDisplayType"].ToString().Trim();

                                        lstOptionSpecial.Add(optionSpecial);
									}

								}

                                questionOption.Specials = lstOptionSpecial; // add specials collection to option

                            questionOptionList.Add(questionOption);
                        }
                    }
                }
            }

            return questionOptionList;
        }

        /// <summary>
        /// UpdateQuestionOptionSpecialCore - Updates a question option special
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option Special must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="specialId"></param>
        /// <param name="specialName"></param>
        /// <param name="specialDisplayType"></param>
        /// <param name="optionId"></param>
        /// <returns></returns>
        public void UpdateQuestionOptionSpecialCore(int specialId, string specialName, string specialDisplayType, int optionId)
        {
            
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                using (var command = new SqlCommand("dbo.UpdateQuestionOptionSpecialCore", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@specialID", specialId);
                    command.Parameters.AddWithValue("@specialName", specialName);
                    command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);
                    command.Parameters.AddWithValue("@optionID", optionId);

                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// UpdateQuestionOptionCore - Updates a question option
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="optionSortOrder"></param>
        /// <param name="optionName"></param>
        /// <param name="optionLabel"></param>
        /// <param name="optionCSS"></param>
        /// <param name="optionHelpURL"></param>
        /// <param name="optionActive"></param>
        /// <returns></returns>
        internal void UpdateQuestionOptionCore(int optionId, int optionSortOrder, string optionName, string optionLabel, string optionCSS, string optionHelpURL, int optionActive)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                using (var command = new SqlCommand("dbo.UpdateQuestionOptionCore", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@optionID", optionId);
                    command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
                    command.Parameters.AddWithValue("@optionName", optionName);
                    command.Parameters.AddWithValue("@optionLabel", optionLabel);
                    command.Parameters.AddWithValue("@optionCSS", optionCSS);
                    command.Parameters.AddWithValue("@optionHelpURL", optionHelpURL);
                    command.Parameters.AddWithValue("@optionActive", optionActive);


                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// UpdateQuestionOptionSpecialCore - Updates a question option special
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option Special must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="specialName"></param>
        /// <param name="specialDisplayType"></param>
        /// <returns></returns>
        public void InsertQuestionOptionSpecialCore(int optionId, string specialName, string specialDisplayType)
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                using (var command = new SqlCommand("dbo.UpdateQuestionOptionSpecialCore", conn))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@optionID", optionId); 
                    command.Parameters.AddWithValue("@specialName", specialName);
                    command.Parameters.AddWithValue("@specialDisplayType", specialDisplayType);
                    
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// UpdateQuestionOptionCore - Updates a question option
        /// in the database
        /// 
        /// PreConditions:
        /// Question Option must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionSortOrder"></param>
        /// <param name="optionName"></param>
        /// <param name="optionLabel"></param>
        /// <param name="optionCSS"></param>
        /// <param name="optionHelpURL"></param>
        /// <param name="questionId"></param>
        /// <returns></returns>
        public int InsertQuestionOptionCore(int optionSortOrder, string optionName, string optionLabel, string optionCSS, string optionHelpURL, int questionId)
        {
            int nIdentity = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Insert Question Option");

                using (var command = new SqlCommand("dbo.InsertQuestionOptionCore", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@optionSortOrder", optionSortOrder);
                        command.Parameters.AddWithValue("@optionName", optionName);
                        command.Parameters.AddWithValue("@optionLabel", optionLabel);
                        command.Parameters.AddWithValue("@optionCSS", optionCSS);
                        command.Parameters.AddWithValue("@optionHelpURL", optionHelpURL);
                        command.Parameters.AddWithValue("@optionQuestionID", questionId);
                        

                        SqlParameter spReturn = new SqlParameter("@Return_Value", SqlDbType.Int);
                        spReturn.Direction = ParameterDirection.ReturnValue;
                        command.Parameters.Add(spReturn);

                        command.ExecuteNonQuery();

                        nIdentity = Int32.Parse(command.Parameters["@Return_Value"].Value.ToString());
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return nIdentity;
        }

        /// <summary>
        /// UpdateOptionActiveStatusByValue - Updates a question option active status
        /// in the database by provided value
        /// 
        /// PreConditions:
        /// Question must exist in the system
        /// 
        /// PostConditions:
        /// Returns number of records updated
        /// </summary>
        /// <param name="optionId"></param>
        /// <param name="optionActive"></param>
        /// <returns></returns>
        public int UpdateOptionActiveStatusByValue(int optionId, bool optionActive)
        {
            int updateRowsCount = 0;

            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                //set a local transaction
                var transaction = conn.BeginTransaction("Update Option Status");

                using (var command = new SqlCommand("dbo.UpdateOptionActiveStatusByValue", conn, transaction))
                {
                    try
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@optionId", optionId);
                        command.Parameters.AddWithValue("@optionActive", optionActive);

                        updateRowsCount = command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }

            return updateRowsCount;
        }
    }
}